package service;

import java.util.List;

import bean.GoodsAllocation;
import bean.GoodsAllocationRowMapper;
import bean.ProductAttr;
import bean.ProductAttrRowMapper;
import bean.ProductValue;
import bean.ProductValueRowMapper;
import common.BaseDAO;

public class ProductAttrDAO extends BaseDAO {
	//根据商品的三级分类ID获取该产品的所有可选择配置，比如颜色/版本等
	public List<ProductAttr> queryAllAttrByTcid(int tcid){
		String sql="select * from product_attr where attrid in"
				+ "(select attrid from product_value where attr_valueid "
				+ "in(select attr_valueid from goods_allocation "
				+ "where goodsid in(select goodsid from goods_info "
				+ "where third_categoryid=?)))";
		return this.query(sql, new ProductAttrRowMapper(), tcid);
	}
	
	//根据商品的三级分类ID和attrid获取该产品的所有对应配置的可选择参数，比如颜色：红色，蓝色等
	public List<ProductValue> queryAttrValueByTcidAndAttrid(int tcid,int attrid){
		String sql="select * from product_value where attrid=? and "
				+ "attr_valueid in (select attr_valueid "
				+ "from goods_allocation where goodsid in "
				+ "(select goodsid from goods_info where "
				+ "third_categoryid=?))";
		return this.query(sql, new ProductValueRowMapper(), attrid,tcid);
	}
	
	//根据attrid获取配置信息
	public List<ProductAttr> queryByAttrid(int attrid){
		String sql="select * from product_attr where attrid=?";
		return this.query(sql, new ProductAttrRowMapper(), attrid);
	}
	
	//根据goodsid获取配置信息
	public List<ProductValue> queryByGoodsid(int goodsid){
		String sql="select * from product_value where attr_valueid in "
				+ "(select attr_valueid from goods_allocation where goodsid=?)";
		return this.query(sql, new ProductValueRowMapper(), goodsid);
	}
	//根据goodsid获取配置信息
	/*public List<GoodsAllocation> queryByGoodsid(int goodsid){
		String sql="select * from goods_allocation where goodsid=?";
		return this.query(sql, new GoodsAllocationRowMapper(), goodsid);
	}*/
	
	//根据goodsid获取该商品的颜色valueid信息
	public List<GoodsAllocation> queryColoridByGoodsid(int goodsid){
		String sql="select * from goods_allocation "
				+ "where goodsid=? and attr_valueid in "
				+ "(select attr_valueid from product_value "
				+ "where attrid in (select attrid from product_attr "
				+ "where attr_name='颜色'))";
		return this.query(sql, new GoodsAllocationRowMapper(), goodsid);
	}
	
	//根据attrValueid获取配置信息
	public List<ProductValue> queryByAttrValueid(int attrValueid){
		String sql="select * from product_value where attr_valueid=?";
		return this.query(sql, new ProductValueRowMapper(), attrValueid);
	}
	
	//根据tcid和选中的attrValueid找到拥有这种valueid的商品所有的其他可用配置参数
	public List<GoodsAllocation> queryOptionalValueByGoodsidsattrValueid(int attrValueid,int tcid){
		String sql="select * from goods_allocation "
				+ "where goodsid in (select goodsid from goods_allocation "
				+ "where attr_valueid=?) and attr_valueid!=? and "
				+ "goodsid in (select goodsid from goods_info "
				+ "where third_categoryid=?)";
		return this.query(sql, new GoodsAllocationRowMapper(), attrValueid,attrValueid,tcid);
	}
	
	//根据tcid和选中的attrValueid找到拥有这种valueid的商品所有的其他可用配置参数
	public List<GoodsAllocation> queryALLOptionalValueByGoodsidsattrValueid(int attrValueid,int tcid){
		String sql="select * from goods_allocation "
				+ "where goodsid in (select goodsid from goods_allocation "
				+ "where attr_valueid=?) and "
				+ "goodsid in (select goodsid from goods_info "
				+ "where third_categoryid=?)";
		return this.query(sql, new GoodsAllocationRowMapper(), attrValueid,tcid);
	}
}
